package cn.jhc.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import cn.jhc.bean.CartBean;
import cn.jhc.bean.CartItemBean;
import cn.jhc.bean.ShopBean;

public class ShopDAO {
	
	protected void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (SQLException ex) {
				ex.printStackTrace();

			}
		}

	}

	
	protected void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
				stmt = null;
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	
	protected void closePreparedStatement(PreparedStatement pstmt) {
		if (pstmt != null) {
			try {
				pstmt.close();
				pstmt = null;
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	
	protected void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
	
	public List<ShopBean> getAllTitles() throws SQLException {
		List<ShopBean> list = new ArrayList<ShopBean>();
		String sql = "select isbn,title,price,imageFile,address,manufacturers "
				+ "from shop";
		ResultSet result = null;
		Statement stat = null;
		Connection con=null;
		try {
			con = ConnectionFactory.getConnection();
			stat = con.createStatement();
			result = stat.executeQuery(sql);
			while (result.next()) {
				ShopBean s = new ShopBean();
				s.setIsbn(result.getString(1));
				s.setTitle(result.getString(2));
				s.setPrice(result.getDouble(3));
				s.setImageFile(result.getString(4));
				s.setAddress(result.getString(5));
				s.setManufacturers(result.getString(6));
				list.add(s);
			}

			return list;
		} finally {
			closeConnection(con);
			closeStatement(stat);
			closeResultSet(result);
		}
	}
	
	
	
	public List<ShopBean> getprecision(String title, String price,
			String address, String manufacturers) throws SQLException {
		List<ShopBean> list = new ArrayList<ShopBean>();
		StringBuffer sb = new StringBuffer(
				"select isbn,title,imageFile,price,address,manufacturers "
						+ "from shop where ");
		boolean bFlag = false;
		if (!title.equals("")) {
			sb.append("title= " + "'" + title + "'");
			bFlag = true;
		}
		if (!price.equals("")) {
			if (bFlag) {
				sb.append("and price = " + "'" + price + "'");
			} else {
				sb.append("price =" + "'" + price + "'");
				bFlag = true;
			}
		}
		if (!address.equals("")) {
			if (bFlag) {
				sb.append("and address = " + "'" + address + "'");
			} else {
				sb.append("address = " + "'" + address + "'");
				bFlag = true;
			}
		}
		if (!manufacturers.equals("")) {
			if (bFlag) {
				sb.append("and manufacturers = " + "'" + manufacturers + "'");
			} else {
				sb.append("manufacturers = " + "'" + manufacturers + "'");
			}
		}
		Statement st = null;
		ResultSet result = null;
		Connection con=null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.createStatement();
			result = st.executeQuery(sb.toString());
			while (result.next()) {
				ShopBean t = new ShopBean();
				t.setIsbn(result.getString(1));
				t.setTitle(result.getString(2));
				t.setImageFile(result.getString(3));
				t.setPrice(result.getDouble(4));
				t.setAddress(result.getString(5));
				t.setManufacturers(result.getString(6));
				list.add(t);
			}

			return list;
		} finally {
			closeConnection(con);
			closeResultSet(result);
			closeStatement(st);
		}
	}
	
	public List<ShopBean> getkeyword(String keyword) throws SQLException {
		List<ShopBean> list = new ArrayList<ShopBean>();
		String sql = "select isbn,title,price,imageFile,address,manufacturers "
				+ "from shop where title like'%" + keyword + "%'";
		ResultSet result = null;
		Statement stat = null;
		Connection con=null;
		try {
			con = ConnectionFactory.getConnection();
			stat = con.createStatement();
			result = stat.executeQuery(sql);
			while (result.next()) {
				ShopBean s = new ShopBean();
				s.setIsbn(result.getString(1));
				s.setTitle(result.getString(2));
				s.setPrice(result.getDouble(3));
				s.setImageFile(result.getString(4));
				s.setAddress(result.getString(5));
				s.setManufacturers(result.getString(6));
				list.add(s);
			}

			return list;
		} finally {
			closeResultSet(result);
			closeStatement(stat);
			closeConnection(con);
		}
	}
	
	public boolean isAmountEnough(int isbn,int quantity)throws SQLException
	{
		Connection conn =null;
		Statement stmt=null;
		ResultSet rs=null;
		boolean bEnough=false;
		
		try{
			conn=ConnectionFactory.getConnection();
			stmt=conn.createStatement();
			rs=stmt.executeQuery("select amount from shop where isbn=isbn");
			while(rs.next()){
				int amount=rs.getInt(1);
				if(amount>=quantity){
					bEnough=true;
					}
				}
			}
		finally{
			closeResultSet(rs);
			closeStatement(stmt);
			closeConnection(conn);
		}
		return bEnough;
	}
	
	public synchronized void buyShop(CartBean cart)throws SQLException
	{
		Connection conn=null;
		PreparedStatement pstmt=null;
		Iterator<CartItemBean> it=cart.getItems().iterator();
		try{
			conn=ConnectionFactory.getConnection();
			String sql="update shop set amount = amount - ? where isbn = ?";
			pstmt =conn.prepareStatement(sql);
			while(it.hasNext())
			{
				CartItemBean item =(CartItemBean)it.next();
				ShopBean shop=item.getShop();
				String Isbn=shop.getIsbn();
				int quantity=item.getQuantity();
				
				pstmt.setInt(1, quantity);
				pstmt.setString(2, Isbn);
				pstmt.addBatch();
				
			}
			pstmt.executeBatch();
		}
		finally{
			closeConnection(conn);
			closePreparedStatement(pstmt);
		}
	}
	
	public ShopBean getShop(String isbn) throws SQLException {
		ShopBean t = new ShopBean();
		String sql = "select isbn,title,imageFile,price,address,manufacturers,amount "
				+ "from shop where isbn=?";
		PreparedStatement ps = null;
		ResultSet result = null;
		Connection conn=null;
		try {
			conn = ConnectionFactory.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, isbn);
			result = ps.executeQuery();
			if (result.next())
			{
				t.setIsbn(result.getString(1));
				t.setTitle(result.getString(2));
				t.setImageFile(result.getString(3));
				t.setPrice(result.getDouble(4));
				t.setAddress(result.getString(5));
				t.setManufacturers(result.getString(6));
				t.setAmount(result.getInt(7));
			}

			return t;
		} finally {
			closeConnection(conn);
			closeResultSet(result);
			closePreparedStatement(ps);
		}
	}
	
	public synchronized void addlist(String user,String Class,String address,String contact,
			String phone,CartBean cart)throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="insert into list(user,class,address,contact,phone,totalprice,date) values(?,?,?,?,?,?,now())";
			pstmt =conn.prepareStatement(sql);
			double totalprice=cart.getTotalPrice();
			pstmt.setString(1, user);
			pstmt.setString(2, Class);
			pstmt.setString(3, address);
			pstmt.setString(4, contact);
			pstmt.setString(5, phone);
			pstmt.setDouble(6, totalprice);
			pstmt.addBatch();
			pstmt.executeBatch();
			
		}finally{
			closeConnection(conn);
			closePreparedStatement(pstmt);
		}
	}
	
	public synchronized void addlistgoods(String user,CartBean cart)throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt=null;
		Iterator<CartItemBean> it=cart.getItems().iterator();
		try{
			conn=ConnectionFactory.getConnection();
			String sql="insert into listgoods(user,title,price,amount,totalprice,date) values(?,?,?,?,?,now())";
			pstmt =conn.prepareStatement(sql);
			while(it.hasNext())
			{
				CartItemBean item =(CartItemBean)it.next();
				ShopBean shop=item.getShop();
				int quantity=item.getQuantity();
				
				pstmt.setString(1, user);
				pstmt.setString(2, shop.getTitle());
				pstmt.setDouble(3, shop.getPrice());
				pstmt.setInt(4, quantity);
				pstmt.setDouble(5, quantity*shop.getPrice());
				
				pstmt.addBatch();
				
			}
			pstmt.executeBatch();
		}
		finally{
			closeConnection(conn);
			closePreparedStatement(pstmt);
		}
	
	}
	public synchronized List<ShopBean> Checklist(String user)throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<ShopBean> list = new ArrayList<ShopBean>();
		ShopBean shop=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="select user,class,address,contact,phone,totalprice,date from list "
					+"where user=? order by date desc";
			pstmt =conn.prepareStatement(sql);
			pstmt.setString(1, user);
			rs=pstmt.executeQuery();
			while(rs.next()) {
				shop = new ShopBean();
				shop.setUser(rs.getString(1));
				shop.setClass1(rs.getString(2));
				shop.setContact(rs.getString(3));
				shop.setAddress(rs.getString(4));
				shop.setPhone(rs.getString(5));
				shop.setTotalprice(rs.getDouble(6));
				shop.setDate(rs.getDate(7));
				shop.setTime(rs.getTime(7));
				list.add(shop);
				
			}
			
		}finally{
			closeConnection(conn);
			closeResultSet(rs);
			closePreparedStatement(pstmt);
		}
		return list;
		
	} 
	public synchronized List<ShopBean> Checklistgoods(String user,String datetime)throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<ShopBean> list = new ArrayList<ShopBean>();
		ShopBean shop=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="select user,title,price,amount,totalprice,date from listgoods "
					+"where user=? and date="+"'"+datetime+"'"+" order by date desc";
			pstmt =conn.prepareStatement(sql);
			pstmt.setString(1, user);
			rs=pstmt.executeQuery();
			while(rs.next()) {
				shop = new ShopBean();
				shop.setUser(rs.getString(1));
				shop.setTitle(rs.getString(2));
				shop.setPrice(rs.getDouble(3));
				shop.setAmount(rs.getInt(4));
				shop.setTotalprice(rs.getDouble(5));
				shop.setDate(rs.getDate(6));
				shop.setTime(rs.getTime(6));
				list.add(shop);
			}
			}finally{
				closeConnection(conn);
				closePreparedStatement(pstmt);
				closeResultSet(rs);
				
			}
		return list;
	} 
	public synchronized List<ShopBean>adminChecklist(String datetime)throws SQLException{
		Connection conn=null;
		Statement stmt = null;
		ResultSet rs = null;
		List<ShopBean> list = new ArrayList<ShopBean>();
		ShopBean shop=null;
		System.out.println(datetime);
		try{
			conn=ConnectionFactory.getConnection();
			String sql="select user,class,address,contact,phone,totalprice,date from list "
					+"where date like'%"+datetime+"%' order by date desc";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				shop = new ShopBean();
				shop.setUser(rs.getString(1));
				shop.setClass1(rs.getString(2));
				shop.setContact(rs.getString(3));
				shop.setAddress(rs.getString(4));
				shop.setPhone(rs.getString(5));
				shop.setTotalprice(rs.getDouble(6));
				shop.setDate(rs.getDate(7));
				shop.setTime(rs.getTime(7));
				list.add(shop);
			}
		
		}finally{
			closeConnection(conn);
			closeStatement(stmt);
			closeResultSet(rs);
		}
		return list;
	}
}
